﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class CertificateGateway:DBGateway
    {
        public bool SaveCertificate(Certificate certificate)
        {
            try
            {
                EmployeeGateway empGateway = new EmployeeGateway();
                int empId = empGateway.GetEmployeeID(certificate.Employee.EmpCode);
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Insert into tblCertificate values(@empId,@exam,@file)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@exam", SqlDbType.VarChar).Value = certificate.Exam;
                CommandObj.Parameters.Add("@file", SqlDbType.VarChar).Value = certificate.FileName;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during saving Certificate");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Certificate> GetCertificates(Certificate certificate)
        {
            try
            {
                EmployeeGateway empGateway = new EmployeeGateway();
                int empId = empGateway.GetEmployeeID(certificate.Employee.EmpCode);
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblCertificate where EmpID='"+empId+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Certificate>certificates =new List<Certificate>();
                while(reader.Read())
                {
                    Certificate cert=new Certificate();
                    cert.Id = Convert.ToInt32(reader[0]);
                    cert.Exam = reader[2].ToString();
                    cert.FileName = reader[3].ToString();
                    certificates.Add(cert);
                }
                return certificates;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Certificate");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}